package sg.edu.nus.dbd.dao.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import sg.edu.nus.dbd.dao.SQLDebugDAO;
import sg.edu.nus.dbd.formbean.SqlDebugTable;

public class SQLDebugDAOImpl extends BaseDAO implements SQLDebugDAO {
	public List<SqlDebugTable> executeAll(String sql) {
		List<SqlDebugTable> arr = new ArrayList<SqlDebugTable>();
		boolean inQuote = false;
		int last = 0;
		for (int i = 0; i < sql.length(); i++) {
			if (sql.charAt(i) == '\'') {
				inQuote = !inQuote;
			} else if (sql.charAt(i) == ';') {
				if (!inQuote) {
					String s = sql.substring(last, i).trim();
					if (!s.equals("")) {
						arr.add(execute(s));
					}
					last = i + 1;
				}
			}
		}
		String s = sql.substring(last).trim();
		if (!s.equals("")) {
			arr.add(execute(s));
		}
		return arr;
	}

	private SqlDebugTable execute(String sql) {
		if (sql.toLowerCase().startsWith("select")) {
			return executeQuery(sql);
		} else {
			return executeUpdate(sql);
		}
	}

	private SqlDebugTable executeUpdate(String sql) {
		Connection conn = getConnection();
		Statement stmt = null;
		int affectedRows = 0;
		if (conn != null) {
			try {
				stmt = conn.createStatement();
				affectedRows = stmt.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				closeStatement(stmt);
				closeConnection(conn);
			}
		}
		List<String> headers = new ArrayList<String>();
		List<List<String>> body = new ArrayList<List<String>>();
		headers.add("Returns");
		List<String> row = new ArrayList<String>();
		row.add("" + affectedRows);
		body.add(row);

		SqlDebugTable table = new SqlDebugTable();
		table.setSql(sql);
		table.setHeaders(headers);
		table.setBody(body);
		return table;
	}

	private SqlDebugTable executeQuery(String sql) {
		Connection conn = getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		List<String> headers = new ArrayList<String>();
		List<List<String>> body = new ArrayList<List<String>>();

		if (conn != null) {
			try {
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				ResultSetMetaData rsmd = rs.getMetaData();
				int C = rsmd.getColumnCount();
				for (int i = 1; i <= C; i++) {
					headers.add(rsmd.getColumnName(i));
				}
				while (rs.next()) {
					List<String> row = new ArrayList<String>();
					for (int i = 1; i <= C; i++) {
						row.add(rs.getString(i));
					}
					if (body.size() >= 500) {
						for (int i = 0; i < row.size(); i++) {
							row.set(i, "...");
						}
						body.add(row);
						break;
					} else {
						body.add(row);
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				closeStatement(stmt);
				closeResultSet(rs);
				closeConnection(conn);
			}
		}

		SqlDebugTable table = new SqlDebugTable();
		table.setSql(sql);
		table.setHeaders(headers);
		table.setBody(body);
		return table;
	}
}
